package IOTWeb.com.dao.meth;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.commons.lang.StringUtils;

import IOTWeb.com.dao.CarOrPersionCheck;
import IOTWeb.com.dao.ExcepContent;
import IOTWeb.com.dao.File_list;
import IOTWeb.com.utils.ConsJson;
import IOTWeb.com.utils.JDUtil;

public class CheckMeth {
	
    private Connection conn;
    // 传递sql语句
    private Statement stt;
    private ResultSet set;
	private JDUtil jdUtil = new JDUtil();
	
	public void insertCheck(CarOrPersionCheck carCheck) throws IOException, SQLException {
        conn = jdUtil.getConnection("URLDev");
        PreparedStatement ps = conn
                .prepareStatement("insert into carorpersionCheck(car_num, name, age, sex, company, valid_date, expire_date, type) values (?,?,?,?,?,?,?,?)");
        ps.setString(1, carCheck.getCar_num());
        ps.setString(2, carCheck.getName());
        ps.setInt(3, carCheck.getAge());
        ps.setString(4, carCheck.getSex());
        ps.setString(5, carCheck.getCompany());
        ps.setInt(6, carCheck.getValid_date());
        ps.setDate(7, (Date) carCheck.getExpire_date());
        ps.setString(8, carCheck.getType());
        ps.executeUpdate();
        System.out.println("核查表插入成功");

        ps.clearParameters();
        ps.close();
		conn.close();

	}
	
	public String selectCheck(String type) throws IOException, SQLException {
		String result = "";
		CarOrPersionCheck carCheck = new CarOrPersionCheck();
		ArrayList<CarOrPersionCheck> carChecks = new ArrayList<>();
		conn = jdUtil.getConnection("URLDev");
		Statement statement = conn.createStatement();
		// 要执行的SQL语句
		String sql = "select * from carorpersionCheck where type = '" + type +"';";
		ResultSet rs = statement.executeQuery(sql);
		while (rs.next()) {
			carCheck.setId(rs.getInt("id"));
			if(type.equals("car"))
				carCheck.setCar_num(rs.getString("car_num"));		
			carCheck.setType(type);
			carCheck.setName(rs.getString("name"));
			carCheck.setAge(rs.getInt("age"));
			carCheck.setSex(rs.getString("sex"));
			carCheck.setCompany(rs.getString("company"));
			carCheck.setValid_date(rs.getInt("valid_date"));
			carCheck.setExpire_date(rs.getDate("expire_date"));
			carChecks.add(carCheck);
		}
		result = ConsJson.Object2Json(carChecks);
		System.out.println(result);
		jdUtil.releaseConnection(conn, statement, rs);
		return result;
	}
	
	public String selectOneCheck(Integer id) throws IOException, SQLException {
		ConsJson cj = new ConsJson();
		String result = "";
		CarOrPersionCheck carCheck = new CarOrPersionCheck();
		ArrayList<CarOrPersionCheck> carChecks = new ArrayList<>();
		conn = jdUtil.getConnection("URLDev");
		Statement statement = conn.createStatement();
		// 要执行的SQL语句
		String sql = "select * from carorpersionCheck where id = " + id +";";
		ResultSet rs = statement.executeQuery(sql);
		while (rs.next()) {
			carCheck.setId(rs.getInt("id"));
			carCheck.setType(rs.getString("type"));
			if(carCheck.getType().equals("car"))
				carCheck.setCar_num(rs.getString("car_num"));				
			carCheck.setName(rs.getString("name"));
			carCheck.setAge(rs.getInt("age"));
			carCheck.setSex(rs.getString("sex"));
			carCheck.setCompany(rs.getString("company"));
			carCheck.setValid_date(rs.getInt("valid_date"));
			carCheck.setExpire_date(rs.getDate("expire_date"));
			carChecks.add(carCheck);
		}
		result = cj.Object2Json(carChecks);
		System.out.println(result);
		jdUtil.releaseConnection(conn, statement, rs);
		return result;
	}
	
	public void deletCheck(Integer id) throws IOException, SQLException {
        conn = jdUtil.getConnection("URLDev");
        PreparedStatement ps = conn
                .prepareStatement("delete from carorpersionCheck where id = "+id);
        ps.executeUpdate();
    	conn.close();
    	ps.close();
	}
}
